package SQL;

import java.sql.*;
import java.util.Scanner;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * Author: 保护小周
 * Date: 2022-11-08
 * Time: 9:11
 */
public class Score implements Configuration {
    private static void field() { //字段
        System.out.println("学号      " + "姓名  "+ "数学 " + "语文 " + "英语 " + "Java");
    }

    //显示学生成绩表
    public static void show() {
        // 加载驱动
        try {
            Class.forName(dirver);
            System.out.println("驱动加载成功！");
            // 链接数据库,获得链接对象

            Connection connection = DriverManager.getConnection(
                    url, username, password);
            System.out.println("数据库连接成功！");

            // 通过连接对象，创建执行环境
            Statement statement = connection.createStatement();

            String sql = "SELECT student.id,student.`name`,score.Chinese,score.English,score.math,score.Java FROM student RIGHT JOIN score on student.id=score.id WHERE student.id=score.id";

            // 执行SQL 语句 得到结果集
            ResultSet resultSet = statement.executeQuery(sql); //sql 查询语句


            if(resultSet == null) {
                System.out.println("查无此人！");
                return;
            }
            field(); //字段
            while (resultSet.next()) {
                String id = resultSet.getString("student.id");
                String name = resultSet.getString("name");
                int chinese = resultSet.getInt("Chinese");
                int english = resultSet.getInt("English");
                int math = resultSet.getInt("Math");
                int java = resultSet.getInt("Java");

                System.out.print(id + " ");
                System.out.print(name + " ");
                System.out.print(chinese + " ");
                System.out.print(english + " ");
                System.out.print(math + " ");
                System.out.print(java + " ");

                System.out.println();
            }

        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("驱动加载失败！");
            System.out.println("数据库连接失败！");
            System.out.println("查询失败！");
        }
    }

    public static void delScore() throws ClassNotFoundException, SQLException {

        //注册jdbc驱动
        Class.forName(dirver);
        System.out.println("连接数据库中……");

        Scanner in = new Scanner(System.in);
        System.out.println("请输入要删除学生的id");
        String delId = in.nextLine();

        //打开链接
        Connection connection = DriverManager.getConnection(url, username, password);
        Statement statement = connection.createStatement();

        String sql = "delete from score where id = " +delId+"";

        final int i = statement.executeUpdate(sql);
        if(i == 0) {
            System.out.println("查无此人！");
            return;
        }
        System.out.println("删除成功!");
        connection.close();
    }

    public static void addScore() throws ClassNotFoundException, SQLException {
        //注册jdbc驱动
        Class.forName(dirver);

        Scanner in = new Scanner(System.in);

        System.out.println("请输入学生id:");
        String id = in.nextLine();

        System.out.println("请输入数学成绩:");
        int math = in.nextInt();

        System.out.println("请输入语文成绩：");
        int Chinese = in.nextInt();

        System.out.println("请输入英语成绩：");
        int english = in.nextInt();

        System.out.println("请输入Java成绩：");
        int java = in.nextInt();

        //打开链接--连接mysql
        Connection connection = DriverManager.getConnection(url, username, password);
        Statement statement = connection.createStatement();

        String sql = "INSERT INTO Score values("+id+",'"+math+"',"+Chinese+", "+ english +", "+java+")";
        //执行sql语句
        statement.executeUpdate(sql);

        System.out.println("添加成功");
        connection.close();
    }

    public static void findStuScore(String id1) {

        try {
            Class.forName(dirver);
            System.out.println("驱动加载成功");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("驱动加载失败！");
        }

        try {
            Connection connection = DriverManager.getConnection(
                    url, username, password);
            System.out.println("数据库连接成功");
            //创建执行环境
            Statement statement = connection.createStatement();

            String sql = "SELECT student.id,student.`name`,score.Chinese,score.English,score.math,score.Java FROM student RIGHT JOIN score on student.id=score.id WHERE "+id1+" = score.id";
            // 执行SQL 语句 得到结果集
            ResultSet resultSet = statement.executeQuery(sql); //sql 查询语句
            if(resultSet.next()) {
                System.out.println("查询无果，请确认是否有该学号的学生");
                return;
            }
            field(); //字段
            while (resultSet.next()) {
                String id = resultSet.getString("student.id");
                String name = resultSet.getString("name");
                int chinese = resultSet.getInt("Chinese");
                int english = resultSet.getInt("English");
                int math = resultSet.getInt("Math");
                int java = resultSet.getInt("Java");

                System.out.print(id + " ");
                System.out.print(name + " ");
                System.out.print(chinese + " ");
                System.out.print(english + " ");
                System.out.print(math + " ");
                System.out.print(java + " ");

                System.out.println();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            System.out.println("数据库连接失败");
        }


    }

    public static void menu() {
        System.out.println();
        System.out.println("------------修改学生信息界面------------");
        System.out.println("**************************************");
        System.out.println("***** 1-修改数学成绩  2-修改语文成绩 *****");
        System.out.println("***** 3-修改英语成绩  4-修改Java成绩*****");
        System.out.println("*****          0-退出此界面        *****");
        System.out.println("**************************************");

        System.out.print("请选择修改内容：");
    }

    //修改数据
    public static void modify() {

        Scanner in = new Scanner(System.in);

        String sql = null;
        boolean flag = true;
        //String sql = "UPDATE student SET id = "+id2+",name = '"+name2+"' WHERE id = "+id1+";";
        System.out.println("请输入要修改的学生id:");
        String id1 = in.nextLine();

        //注册jdbc驱动
        try {
            Class.forName(dirver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("驱动加载失败！");
        }
        //打开链接
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url,username, password);
            System.out.println("连接成功！");
            //创建执行环境
            Statement statement = connection.createStatement();


            while(flag) {
                menu();
                System.out.println("请输入要修改的成绩");
                int size = in.nextInt();
                in.nextLine();

                switch (size) {
                    case 1:
                        UPMath(id1);
                        System.out.println("修改成功");
                        break;
                    case 2:
                        UPChine(id1);
                        System.out.println("修改成功");
                        break;
                    case 3:
                        UPEnglish(id1);
                        System.out.println("修改成功");
                        break;
                    case 4:
                        UPJava(id1);
                        System.out.println("修改成功");
                        break;
                    case 0:
                        flag = false;
                        connection.close();
                        break;
                    default:
                        System.out.println("输入错误！");
                        break;
                }
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }



    }

    //修改数学成绩
    private static void UPMath(String id1) {
        Scanner in = new Scanner(System.in);

        try {
            Class.forName(dirver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("驱动加载失败！");
        }

        try {
            Connection connection = DriverManager.getConnection(
                    url, username, password);
            //创建执行环境
            Statement statement = connection.createStatement();

            System.out.println("请输入修改后的成绩：");
            int math = in.nextInt();

            String sql = "UPDATE score SET Math = "+math+"  WHERE id = "+id1+";";
            // 执行SQL 语句 得到结果集
            statement.executeUpdate(sql);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
            System.out.println("数据库连接失败");
        }
    }

    //修改语文成绩
    private static void UPChine(String id1) {
        Scanner in = new Scanner(System.in);

        try {
            Class.forName(dirver);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("驱动加载失败！");
        }

        try {
            Connection connection = DriverManager.getConnection(
                    url, username, password);
            //创建执行环境
            Statement statement = connection.createStatement();

            System.out.println("请输入修改后的成绩：");
            int china = in.nextInt();

            String sql = "UPDATE score SET Chinese = "+china+"  WHERE id = "+id1+";";
            // 执行SQL 语句 得到结果集
            statement.executeUpdate(sql);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
            System.out.println("数据库连接失败");
        }
    }

    //修改英语成绩
    private static void UPEnglish(String id1) {
        Scanner in = new Scanner(System.in);

        try {
            Class.forName(dirver);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("驱动加载失败！");
        }

        try {
            Connection connection = DriverManager.getConnection(
                    url, username, password);
            //创建执行环境
            Statement statement = connection.createStatement();

            System.out.println("请输入修改后的成绩：");
            int english = in.nextInt();

            String sql = "UPDATE score SET English = "+english+"  WHERE id = "+id1+";";
            // 执行SQL 语句 得到结果集
            statement.executeUpdate(sql);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
            System.out.println("数据库连接失败");
        }
    }

    //修改Java成绩
    private static void UPJava(String id1) {
        Scanner in = new Scanner(System.in);

        try {
            Class.forName(dirver);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("驱动加载失败！");
        }

        try {
            Connection connection = DriverManager.getConnection(
                    url, username, password);
            //创建执行环境
            Statement statement = connection.createStatement();

            System.out.println("请输入修改后的成绩：");
            int java = in.nextInt();

            String sql = "UPDATE score SET Java = "+java+"  WHERE id = "+id1+";";
            // 执行SQL 语句 得到结果集
            statement.executeUpdate(sql);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
            System.out.println("数据库连接失败");
        }
    }


}
